from eim.tools.dbtool import DBTool
from eim.tools.searchtool import MatchLogic,PartnerRS,SearchCondition,SearchConditionList
from eim.DataBasModals.mode import t_project,t_userinfo
from eim.result.page_result import PageResult
from eim.result.simple_result import SimpleResult
from flask import current_app
from sqlalchemy import func,select,update,delete,text,or_,and_,insert,join
from sqlalchemy.engine import LegacyRow,Connection,CursorResult

class ProjectService:
    """
    项目维护服务类
    """
    def __init__(self,condb):
        self.db = condb
        
    def _chkPrjNumber(self,prjnum:str)->bool:
        """
        校验项目编号是否能用

        Args:
            prjnum (str): 传入的手工项目编号，表中存在即不能用，否则为能用

        Returns:
            bool: 校验结果
        """
        try:
            con:Connection=self.db.engine.connect()
            qry= select(func.count())
            qry=qry.where(t_project.c.pnumber==prjnum)
            res:CursorResult = con.execute(qry)
            cnt = res.scalar()
            if(cnt>0):
                return False
            else:
                return True
        except Exception as es:
            return es
        
    def findPrjIDfromCode(self,prjcode:str)->dict:
        """
        获取指定项目编码的项目id

        Args:
            prjcode (str): 项目编码

        Returns:
            dict: 项目信息
        """
        try:
            con:Connection = self.db.engine.connect()
            qry = select(t_project)
            qry=qry.where(t_project.c.pcode==prjcode)
            res:CursorResult = con.execute(qry)
            prj:LegacyRow = res.first()
            if prj == None:
                raise Exception("未找到对应项目编号的项目")
            else:
                return prj._asdict()
        except Exception as es:
            raise
            
    def appendProject(self,pname:str,prjnumber:str,pcomment:str)->int:
        """
        添加新的项目

        Args:
            pname (str): 项目名称
            prjnumber(str): 手工项目编号
            pcomment (str): 项目描述

        Returns:
            int: 返回新添加的项目id
        """
        try:
            if (prjnumber==''):
                raise Exception("手工项目编号不能为空")
            
            if (not self._chkPrjNumber(prjnumber)):
                raise Exception("指定的手工项目编号已经被使用")
            
            
            con = self.db.engine.connect()
            dtool = DBTool(self.db)
           
            pcode = dtool.getNewCode('project','prj','',6)
            val={
                "pcode": pcode,
                "pnumber": prjnumber,
                "pname": pname,
                "pcomment": pcomment
            }
            qry = insert(t_project).values(val)
            res:CursorResult = con.execute(qry)
            return res.inserted_primary_key[0]
        except Exception as es:
            raise es
    
    def _checkPrjNumByid(self,id:int,prjnumber:str)->bool:
        """
        根据项目ID与项目手工编码校验手工项目编号能否使用

        Args:
            prjnumber (str): 手工编码

        Returns:
            bool: 该项目号能否被使用
        """
        try:
            con = self.db.engine.connect()
            qry = select(func.count())
            qry = qry.where(or_(t_project.c.pnumber==prjnumber,t_project.c.ID==id))
            res:CursorResult = con.execute(qry)
            cnt = res.scalar()
            if(cnt>1):
                return False
            else:
                return True
                
        except Exception as es:
            raise es;
         
    def updateProject(self,id:int,pms:dict)->int:
        """
        更新项目信息

        Args:
            id (int): 项目ID
            pms (dict): 项目更新参数

        Returns:
            int: 受影响的条目数量
        """
        try:
            if("pnumber" in pms.keys()):
                prjnumber=pms["pnumber"]
                if(not self._checkPrjNumByid(id,prjnumber)):
                    raise Exception("指定的手工项目编号不能重复使用")

            con = self.db.engine.connect()
            qry = update(t_project).values(pms)
            qry=qry.where(t_project.c.ID==id)
            res:CursorResult = con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise es
        
    def getPrjByPnumber(self,Pnumber:str)->dict:
        """
        根据手工项目编号获取项目信息

        Args:
            Pnumber (str): 手工项目编号

        Returns:
            dict: 项目信息
        """
        try:
            con = self.db.engine.connect()
            qry = select(t_project).where(t_project.c.pnumber==Pnumber)
            res:CursorResult = con.execute(qry)
            p=res.first()
            if(p==None):
                raise Exception("未找到指定项目编号的项目信息")
            else:
                return p._asdict()
        except Exception as es:
            raise es
        

    def pageProject(self,pn:int,ps:int,fields:list,pmlist:SearchConditionList)->dict:
        """
        分页检索项目信息

        Args:
            pn (int): 分页检索页码
            ps (int): 分页检索页面尺寸
            fields (list): 检索的字段列表
            pmlist (SearchConditionList): 条件列表

        Returns:
            dict: 返回分页结果
        """
        try:
            pn=1 if pn<=0 else pn
            ps=30 if pn<=0 else ps
            offset=(pn-1)*ps
            
            if(not isinstance(fields,list)):
                raise Exception("字段列表必须为List")
            if(not isinstance(pmlist,SearchConditionList)):
                raise Exception("检索条件不正确")
            from eim.tools.dbtool import DBTool
            dt = DBTool(self.db)
            fds = dt.genDBcolumns(t_project,fields)
            str_pms = pmlist.getConditionStr()
            
            
           
            if str_pms!=None:
                qry_rs= select(fds).where(text(str_pms))
                qry_cnt = select(func.count(t_project.c.ID)).where(text(str_pms))
            else:
                qry_rs= select(fds)
                qry_cnt = select(func.count(t_project.c.ID))
            qry_rs= qry_rs.limit(ps).offset(offset)
            con = self.db.engine.connect()
            res:CursorResult = con.execute(qry_cnt)
            total = res.scalar()
            
            res:CursorResult = con.execute(qry_rs)
            result:list=[]
            for row in res.fetchall():
                result.append(row._asdict())
            
            from eim.result.page_result import PageResult
            pg =PageResult(pn,ps,total,result)
            return pg.toDict()
        except Exception as es:
            raise es
        
    def deleteByID(self,id:int)->int:
        """
        根据id删除指定项目信息

        Args:
            id (int): 待删除的项目id

        Returns:
            int: 受影响行数
        """
        try:
            con = self.db.engine.connect()
            joinqry = select(func.count(t_userinfo.c.ID)).join(t_project,t_userinfo.c.pcode==t_project.c.pcode).where(t_project.c.ID==id)
            res:CursorResult = con.execute(joinqry)
            if(res.scalar()>0):
                raise Exception("待删除的项目正在被使用,不能删除")
            else:
                qry=delete(t_project).where(t_project.c.ID==id)
                res:CursorResult = con.execute(qry)
                return res.rowcount
        except Exception as es:
            raise es
    
    def getPrjLoopupData(self)->dict:
        """
        获取prj用于lookup的数据

        Returns:
            dict: 结果列表
        """
        try:
            result = []
            con:Connection = self.db.engine.connect()
            qry=select(t_project.c.pcode,t_project.c.pname)
            res:CursorResult = con.execute(qry)
            for item in res.fetchall():
                result.append(item._asdict())
            return result
        except Exception as es:
            raise es